package com.scalar.chiptrack.operations.dataentry.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.dataentry.DieBankBinningInfoWafer;

import java.util.StringTokenizer;
import java.util.ArrayList;
import java.util.Iterator;
import java.sql.*;

import org.apache.struts.util.LabelValueBean;

/**
 * User: Murali Krishna Yengala
 * Date: Jul 23, 2005
 * Time: 9:18:54 AM
 */
public class DieBankBinningInfoDAO {
    public static ArrayList getSortLots( int processStepId, int locationId ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList lotNumberList = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareCall ( "select Lot_Table.Lot_ID,Lot_Number from Lot_Table, Process_Step where Lot_Table.Processstep_Id = Process_Step.Processstep_Id and Lot_Table.Processstep_Id=? and lot_current_status = 'close' and Location_Id=? and Process_Step.PS_Type_Name like 'SORT' and (select count(*) from informative_binning where lot_id = lot_table.lot_id) > 0 ORDER BY Lot_Number" );
            pstmt.setInt ( 1, processStepId );
            pstmt.setInt ( 2, locationId );

            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                int lotId = rs.getInt(1);
                String lotNumber = rs.getString(2);
                lotNumberList.add( new LabelValueBean( lotNumber, ""+lotId));
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load Sort Lot Numbers from database", sqle );
        }

        finally
        {
            if ( rs != null )
            {
                try {     rs.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( pstmt != null )
            {
                try {     pstmt.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( conn != null )
            {
                try {    conn.close ();     }     catch ( SQLException ignore )      {      }
            }
        }
        return lotNumberList;
    }

    public static ArrayList loadWafersForSortLot(int lotId, int goodBinsCount) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        CallableStatement pstmt = null;
        ArrayList wafersList = new ArrayList();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareCall ( "{CALL sp_get_wafers_for_die_bank_binning_info(?, ?)}" );
            pstmt.setInt ( 1, lotId );
            pstmt.setInt ( 2, goodBinsCount );

            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                DieBankBinningInfoWafer wafer = new DieBankBinningInfoWafer();
                wafer.setWaferId( rs.getString(1));
                String binQuantities = rs.getString(2);
                StringTokenizer strTokenizer = new StringTokenizer( binQuantities, ";");
                ArrayList binQuantitiesList = new ArrayList();
                while( strTokenizer.hasMoreTokens() )
                {
                    String nextToken = strTokenizer.nextToken();
                    nextToken = nextToken == null ? "" : nextToken.trim();
                    nextToken = "0".equalsIgnoreCase(nextToken) ? "" : nextToken;
                    BinQuantity binQuantity = new BinQuantity();
                    binQuantity.setBinQuantity( nextToken);
                    binQuantitiesList.add( binQuantity );
                }
                wafer.setBinQuantities( binQuantitiesList);
                int binRejected = rs.getInt(3);
                wafer.setBinRejected( (binRejected==0?"":""+binRejected) );
                wafer.setYield( rs.getFloat(4));
                wafer.setEditable( (rs.getInt(5)==0) );
                wafer.setHold( (rs.getInt(6)==0) );
                wafer.setBinsUsedForAssembly( rs.getString(7));
                wafersList.add( wafer);
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load Wafers for Sort Lot", sqle );
        }

        finally
        {
            if ( rs != null )
            {
                try {rs.close ();}     catch ( SQLException ignore )    {}
            }
            if ( pstmt != null )
            {
                try {pstmt.close ();}     catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try {conn.close ();}     catch ( SQLException ignore )  {}
            }
        }
        return wafersList;
    }

    public static void saveSortLotWafers( DBTransaction dbTransaction, int lotId, int processStepId, int goodBinsCount, ArrayList wafersList) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        CallableStatement pstmt = null;

        try
        {
            conn = dbTransaction.getConnection ();
            pstmt = conn.prepareCall ( "{CALL sp_save_die_bank_binning_info_wafers(?, ?, ?, ?, ?, ?, ?, ?)}" );
            //lotid, processstepid, goodbinscount, waferid, binquantities, binrejected, yield

            Iterator wafersIterator = wafersList.iterator();
            while ( wafersIterator.hasNext() )
            {
                int binRejected = 0;
                DieBankBinningInfoWafer dieBankBinningInfoWafer = (DieBankBinningInfoWafer) wafersIterator.next();
                String waferId = dieBankBinningInfoWafer.getWaferId();
                try
                {
                    Integer.parseInt( waferId);
                }
                catch ( NumberFormatException e)
                {
                    continue;
                }
                ArrayList binQuantitiesList = dieBankBinningInfoWafer.getBinQuantities();
                Iterator binQtyIterator = binQuantitiesList.iterator();
                String binQtuantities = "";
                while( binQtyIterator.hasNext() )
                {
                    BinQuantity binQuantity = (BinQuantity) binQtyIterator.next();
                    String binQty =  binQuantity.getBinQuantity();
                    binQty = ((binQty == null || "".equals(binQty.trim())) ? "0" : binQty.trim());
                    binQtuantities = binQtuantities + binQty + ";";
                }
                pstmt.setInt ( 1, lotId );
                pstmt.setInt ( 2, processStepId );
                pstmt.setInt ( 3, goodBinsCount);
                pstmt.setInt ( 4, Integer.parseInt(dieBankBinningInfoWafer.getWaferId().trim()));
                pstmt.setString( 5, binQtuantities);
                try
                {
                    binRejected = Integer.parseInt(dieBankBinningInfoWafer.getBinRejected().trim());
                }
                catch( NumberFormatException ne)
                {
                    binRejected = 0;
                }
                pstmt.setInt ( 6, binRejected);
                pstmt.setFloat( 7, dieBankBinningInfoWafer.getYield() );
                pstmt.setInt( 8, (dieBankBinningInfoWafer.isHold()?0:1) );
                pstmt.executeUpdate();
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not save Wafers for Sort Lot", sqle );
        }

        finally
        {
            if ( rs != null )
            {
                try {rs.close ();}     catch ( SQLException ignore )    {}
            }
            if ( pstmt != null )
            {
                try {pstmt.close ();}     catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try {conn.close ();}     catch ( SQLException ignore )  {}
            }
        }
    }

    public static int getGrossDieFromSortLot( int lotId ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        int grossDie = 0;
        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareCall ( "select SL_Die_Wafer from Sort_Lot where lot_id = ?" );
            pstmt.setInt ( 1, lotId );

            rs = pstmt.executeQuery ();

            if ( rs.next () )
            {
                grossDie = rs.getInt( 1);
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load Gross Die Value for the Sort Lot", sqle );
        }

        finally
        {
            if ( rs != null )
            {
                try {     rs.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( pstmt != null )
            {
                try {     pstmt.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( conn != null )
            {
                try {    conn.close ();     }     catch ( SQLException ignore )      {      }
            }
        }
        return grossDie;
    }

}
